#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#include <string.h>
#include <errno.h>
#include "database.h"

#define TABLE_NAME     "Mydata"

static sqlite3 *db;
static int callback_check_table(void *param, int argc, char **argv, char **azColName);

int db_create(char *filename)  
{  
	char 		*zErrMsg = 0;  
	int 		ret;  
	char 		sql[SQL_COMMAND_LEN]={0};

	//打开指定的数据库文件,如果不存在将创建一个同名的数据库文件  
	ret = sqlite3_open(filename, &db);  
	if(ret)  
	{  
		printf("Open ERROR! ERRMSG:%s\n", sqlite3_errmsg(db));  
		sqlite3_close(db);  
		return -1; 
	}
	else 
	{
		printf("Open database success!\n");  
	}

	//create table
	sprintf(sql,"create table if not exists '%s'(DEVSN VARCHAR(20), TIME VARCHAR(50), TEMPERATURE FLOAT);", TABLE_NAME);
	ret=sqlite3_exec(db,sql,NULL,NULL,NULL);
	if(ret!=SQLITE_OK)
	{
		printf("sqlite3_exec_create_table: %s\n", sqlite3_errmsg(db));
		return -2;
	}
	else
	{
		printf("Create table ['%s'] success!\n", TABLE_NAME);  
	}

	return 0;  
}  

int db_insert(packet_t *pack)  
{  
	char 		*zErrMsg = 0;  
	int 		ret;  
	char 		sql[SQL_COMMAND_LEN]={0};

	//clean the sql
	memset(sql, 0, sizeof(sql));
	sprintf(sql, "insert into '%s' (DEVSN, TIME, TEMPERATURE) VALUES ('%s', '%s', '%f');",
			TABLE_NAME, pack->devsn, pack->time, pack->temp);

	//exert the sqlite3_exec
	ret=sqlite3_exec(db,sql,NULL,NULL,NULL);
	if(ret!=SQLITE_OK)
	{
		printf("sqlite3_exec_insert_table: %s\n",sqlite3_errmsg(db));
		exit(1);
	}
	else 
	{
		printf("Insert data success!\n");  
	}

	return 0;  
}  

int db_delete(void)//删除数据库的第1条数据
{
	int                     ret;
	char                    sql[SQL_COMMAND_LEN];

	//delete the first data
	memset(sql, 0, sizeof(sql));
	snprintf(sql, sizeof(sql), "delete from '%s' limit 1;", TABLE_NAME);
	ret=sqlite3_exec(db,sql,NULL,NULL,NULL);
	if(ret!=SQLITE_OK)
	{
		printf("sqlite3_exec_delete the first data error: %s  strerror:%s\n",sqlite3_errmsg(db),strerror(errno));
		return -1;
	}
	else
	{
		printf("delete the first data successfully!\n");
	}

	return 0;
}

/* Description: Close sqlite database handler */
int db_close(void)
{
	int ret;

	//关闭数据库
	ret=sqlite3_close(db);
	if(ret!=SQLITE_OK)
	{
		printf("sqlite3_exec_close_database: %s\n",sqlite3_errmsg(db));
	}
	printf("close_database success!\n");

	return 0;
}

int db_firstdata( packet_t *pack )
{
	char                    *err_msg = NULL;
	int                     ret;
	char					**results = NULL;
	int 					rows;
	int 					columns;
	char                    sql_se[SQL_COMMAND_LEN];
	int						 has_data = 0;

	memset(sql_se, 0, sizeof(sql_se));
	sprintf(sql_se, "SELECT EXISTS(SELECT 1 FROM '%s' LIMIT 1);", TABLE_NAME);

	ret = sqlite3_exec(db, sql_se, callback_check_table, &has_data, &err_msg);
	if (ret != SQLITE_OK)
	{
		printf("SQL select error: %s\n", err_msg);
		sqlite3_free(err_msg);
		return -1;
	}

	if (!has_data)
	{
		printf("The table is empty.\n");
		return 0;
	}

	memset(sql_se, 0, sizeof(sql_se));
	sprintf(sql_se, "select * from '%s' limit 1;", TABLE_NAME);

	ret = sqlite3_get_table(db, sql_se, &results, &rows, &columns, &err_msg);
	if (ret != SQLITE_OK)
	{
		printf("SQL select2 error: %s\n", err_msg);
		sqlite3_free(err_msg);
		sqlite3_free_table(results); 
		return -1;
	}
	else
	{
		snprintf(pack->devsn, sizeof(pack->devsn), "%s", results[3]);
		snprintf(pack->time, sizeof(pack->time), "%s", results[4]);
		pack->temp = atof(results[5]);
		printf("pack->devsn=%s, pack->time=%s, pack->temp=%f\n", pack->devsn, pack->time, pack->temp);
		printf("The first row data in the table has been packed successfully!\n");        
	}

	sqlite3_free_table(results);

	return 1;
}

static int callback_check_table(void *param, int argc, char **argv, char **azColName)
{
	int *has_data = (int *)param;

	if (argc >= 1 && argv[0] != NULL)
	{
		*has_data = atoi(argv[0]);
	}

	return 0;
}
